﻿/*===============================================================================
TSL_LoadTestCoreInfo

This table contains all of the data (not already in the LoadTestRun table) that 
is used for creating the Summary Report. It is either calculated at the end of
the run (for all tests executed after the tool is installed) or during tool
install and configuration.
===============================================================================*/
	--START CODE--
	CREATE TABLE TSL_LoadTestCoreInfo(
		LoadTestRunId	INT NOT NULL
		,AgentCount		INT NULL
		,MaxUsers		INT NULL
		,SampleInterval INT NULL
	
		,TotalTests		INT NULL
		,PassedTests	INT NULL
		,FailedTests	INT NULL
		,AvgTT_All		FLOAT NULL			--Avg Test Time 
		,TestsSec_All	FLOAT NULL

		,TotalTrans		INT NULL			
		,AvgTrET_All	FLOAT NULL			--Avg Trans Elapsed Time
		,AvgTrRT_All	FLOAT NULL			--Avg Trans Response Time
		,TransSec_All	FLOAT NULL			--Transactions Per Second - All

		,TotalPages		INT NULL			
		,PagesSec_All	FLOAT NULL			--PagesPerSec - All
		,AvgPgT_All		FLOAT NULL			--Avg Page Time - All
		
		,TotalRequests	INT NULL
		,PassedRequests	INT NULL
		,FailedRequests	INT NULL
		,CachedRequests	INT NULL			--Number of cached requests
		,ReqSec_All		FLOAT NULL			--Requests Per Sec - All
		,AvgReqT_All	FLOAT NULL			--Avg Request Time - All
		,AvgReqLen_All	FLOAT NULL			--Avg Request Content Length - All

		,AvgTT_Pass			FLOAT NULL			
		,TestsSec_Pass		FLOAT NULL
		,AvgTrET_Pass		FLOAT NULL			
		,AvgTrRT_Pass		FLOAT NULL			
		,TransSec_Pass		FLOAT NULL			
		,PassedPages		INT NULL			
		,PagesSec_Pass		FLOAT NULL			
		,AvgPgT_Pass		FLOAT NULL			
	
		,PctTime_WebTest		FLOAT NULL		--Percent of time (by the agents ) spent in the web test code
		,PctTime_Rules			FLOAT NULL		--Percent of time (by the agents ) spent in Rules and Plugins
		,PctTime_LoadPlugin		FLOAT NULL		--Percent of time (by the agents ) spent in any Load Test Plugins

		,FLAG_ContainsPages				bit NULL
		,FLAG_ContainsTransactions		bit NULL
		,FLAG_ContainsDetailedTimingInfo	bit NULL
		,FLAG_ContainsStepLoadProfile	bit NULL
		,FLAG_TestAborted				bit NULL
		,FLAG_LoadTestParsingFailed		bit NULL
	
	PRIMARY KEY CLUSTERED 
		(
			LoadTestRunId ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO


/*===============================================================================
TSL_ScoreCardMachineCPU

This table contains all of the summary perfmon values for CPU Utilization for 
each machine in the test run results. It is used to quickly build scorecard 
charts for run comparisons.
===============================================================================*/
	--START CODE--
	CREATE TABLE TSL_ScoreCardMachineCPU (
		[LoadTestRunId] [int] NOT NULL,
		[MachineName] [nvarchar](255) NOT NULL,
		[InstanceId] [int] NOT NULL,
		[Qty] [real] NULL,
		[Min] [real] NULL,
		[Avg] [real] NULL,
		[Max] [real] NULL,
	PRIMARY KEY CLUSTERED 
	(
		[LoadTestRunId] ASC,
		[MachineName] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO

/*===============================================================================
TSL_ScoreCardMachineAvailableMB

This table contains all of the summary perfmon values for Available MBytes for 
each machine in the test run results. It is used to quickly build scorecard 
charts for run comparisons.
===============================================================================*/
	--START CODE--
	CREATE TABLE TSL_ScoreCardMachineAvailableMB (
		[LoadTestRunId] [int] NOT NULL,
		[MachineName] [nvarchar](255) NOT NULL,
		[InstanceId] [int] NOT NULL,
		[Qty] [real] NULL,
		[Min] [real] NULL,
		[Avg] [real] NULL,
		[Max] [real] NULL,
	PRIMARY KEY CLUSTERED 
	(
		[LoadTestRunId] ASC,
		[MachineName] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO

/*===============================================================================
TSL_LoadTestIntervals

This table contains all of the time and count information for any constant
intervals that are in the test run. The data is populated by the reporting
tool.
===============================================================================*/
	--START CODE--
	CREATE TABLE TSL_LoadTestIntervals (
		[LoadTestRunId] [int] NOT NULL,
		[StartOffset] [int] NOT NULL,
		[EndOffset] [int] NOT NULL,
		[UserLoad] [int] NOT NULL,
		[NumberOfDataPoints] [int] NOT NULL,
	PRIMARY KEY CLUSTERED
	(
		[LoadTestRunId] ASC,
		[StartOffset] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO


/*===============================================================================
TSL_StagingTableForPerfmonImports

This table is used as a landing point for importing PERFMON values from CSV
files. The data from the CSV file gets Bulk Copied into this table, and then
the appropriate stored procedures copy the pieces of data from here into the
various PerformanceCounter tables. This table is cleared when the import is
completed.
===============================================================================*/
	--START CODE--
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TSL_StagingTableForPerfmonImports') AND type in (N'U'))
	CREATE TABLE TSL_StagingTableForPerfmonImports(
		[instanceId] [int] NOT NULL,
		[machineName] [nvarchar](255) NOT NULL,
		[categoryName] [nvarchar](255) NOT NULL,
		[counterName] [nvarchar](255) NOT NULL,
		[instanceName] [nvarchar](255) NOT NULL,
		[cumulativeValue] [real] NULL,
	PRIMARY KEY CLUSTERED 
	(
		[instanceId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO

/*===============================================================================
TSL_DbChangeLogs

This table tracks any changes that are made to the results DB by the Reporting
Tool. That way we can track what the reporting tool is doing.
===============================================================================*/
	--START CODE--
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TSL_DbChangeLogs') AND type in (N'U'))
	CREATE TABLE TSL_DbChangeLogs (
		[LoadTestRunId] [int] NOT NULL,
		[LogId] [int] IDENTITY(1,1) NOT NULL,
		[ChangeTypeId] [int] NOT NULL,
		[ChangeTime] [datetime] NOT NULL,
		[LogComment] [nvarchar](1024) NULL,
	PRIMARY KEY CLUSTERED 
	(
		[LogId] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO

/*===============================================================================
TSL_DbChangeLogTypes

This table contains the list of stored procedures that make changes to the DB
and is used to track the owner of any record in the TSL_DbChangeLogs table.
===============================================================================*/
	--START CODE--
	CREATE TABLE TSL_DbChangeLogTypes(
		[ChangeTypeId] [int] NOT NULL,
		[ChangeTypeDescription] [nvarchar](1024) NOT NULL,
	)
	GO

	--START CODE--
	INSERT INTO TSL_DbChangeLogTypes ([ChangeTypeId] ,[ChangeTypeDescription])
		 VALUES  (1, 'TSL_prc_UpdateLoadTestCoreInfoTable')
				,(2, 'TSL_prc_UpdateScoreCardMachineCpuTable')
				,(3, 'TSL_prc_UpdateScoreCardMachineAvailableMBTable')
				,(4, 'TSL_prc_GetStartingValuesForImportingPerfmon')
				,(5, 'TSL_prc_UpdatePerfmonTablesFromImport')
	GO



/*===============================================================================
--TSL_Code for testing the results queries
	--ALTER PROCEDURE TSL_prc_Add_LoadTestCoreInfo_UsingPerfmon
	--	@RunId  int
	--	,@IncludeTest bit = 1
	--	,@IncludeTransaction bit = 1
	--	,@IncludePage bit = 1

--DECLARE @tt TABLE(
--	LTRid INT NULL,
--	DetailedTestCounts INT NULL,
--	SummaryTestCounts INT NULL,
--	PerfmonTestCounts INT NULL,
--	DetailedTransCounts INT NULL,
--	SummaryTransCounts INT NULL,
--	PerfmonTransCounts INT NULL,
--	DetailedPageCounts INT NULL,
--	SummaryPageCounts INT NULL,
--	PerfmonPageCounts INT NULL,
--	ContainsPages				BIT NULL,
--    ContainsTransactions		BIT NULL,
--    ContainsDetailedTimingInfo	BIT NULL
--    IsLocalRun					BIT NULL,
--    ContainsAnalysisNotes		BIT NULL,
--    TestAborted					BIT NULL,
--    LoadTestParsingFailed		BIT NULL


--DECLARE @RunId INT;

--DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
--FOR SELECT LoadTestRunId FROM LoadTestRun;

--OPEN c;

--FETCH NEXT FROM c INTO @RunId;

--WHILE @@FETCH_STATUS = 0
--BEGIN


--		---------------------------------------------------------------------------------
--		-- Core Summary Info
--		---------------------------------------------------------------------------------


--		INSERT INTO @tt (LTRid) SELECT @RunId 
		
---- Test Counts from Test Details Page
--		UPDATE @tt SET DetailedTestCounts = (SELECT COUNT(TestDetailId)	FROM LoadTestTestDetail	WHERE LoadTestRunId = @RunId) WHERE LTRid = @RunId				
---- Test Counts from Perfmon Data
--		UPDATE @tt SET PerfmonTestCounts = (SELECT MAX(ComputedValue) FROM LoadTestComputedCounterSample WHERE LoadTestRunId = @RunId
--				AND CategoryName = 'LoadTest:Test' AND CounterName = 'Total Tests' AND InstanceName = '_Total') WHERE LTRid = @RunId
---- Test Counts from Summary Test Data
--		UPDATE @tt SET SummaryTestCounts = (SELECT SUM(TestsRun) FROM LoadTestTestSummaryData WHERE LoadTestRunId = @RunId) WHERE LTRid = @RunId



---- Transactions Counts from Transactions Details Page
--		UPDATE @tt SET DetailedTransCounts = (SELECT COUNT(TransactionDetailId)	FROM LoadTestTransactionDetail	WHERE LoadTestRunId = @RunId) WHERE LTRid = @RunId				
---- Transactions Counts from Perfmon Data
--		UPDATE @tt SET PerfmonTransCounts = (SELECT MAX(ComputedValue) FROM LoadTestComputedCounterSample WHERE LoadTestRunId = @RunId
--				AND CategoryName = 'LoadTest:Transaction' AND CounterName = 'Total Transactions' AND InstanceName = '_Total') WHERE LTRid = @RunId
---- Transactions Counts from Summary Transactions Data
--		UPDATE @tt SET SummaryTransCounts = (SELECT SUM(TransactionCount) FROM LoadTestTransactionSummaryData WHERE LoadTestRunId = @RunId) WHERE LTRid = @RunId


---- Page Counts from Page Details Page
--		UPDATE @tt SET DetailedPageCounts = (SELECT COUNT(PageDetailId)	FROM LoadTestPageDetail	WHERE LoadTestRunId = @RunId) WHERE LTRid = @RunId				
---- Page Counts from Perfmon Data
--		UPDATE @tt SET PerfmonPageCounts = (SELECT MAX(ComputedValue) FROM LoadTestComputedCounterSample WHERE LoadTestRunId = @RunId
--				AND CategoryName = 'LoadTest:Page' AND CounterName = 'Total Pages' AND InstanceName = '_Total') WHERE LTRid = @RunId
---- Page Counts from Summary Page Data
--		UPDATE @tt SET SummaryPageCounts = (SELECT SUM(PageCount) FROM LoadTestPageSummaryData WHERE LoadTestRunId = @RunId) WHERE LTRid = @RunId



--  FETCH NEXT FROM c INTO @RunId;
--END

--CLOSE c;
--DEALLOCATE c;

--SELECT * FROM @tt

--SELECT LoadTestRunId, LoadTestName, Outcome, Description FROM LoadTestRun ORDER BY LoadTestRunId ASC
===============================================================================*/
